import sqlite3

createAngleDB = """
                CREATE TABLE IF NOT EXISTS angles
                (
                    book_name TEXT UNQIUE NOT NULL,
                    xAngle INTEGER NOT NULL,
                    yAngle INTEGER NOT NULL    
                )
                """

createBookDB = """
                CREATE TABLE IF NOT EXISTS books
                (
                    book_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    book_name TEXT UNIQUE NOT NULL,
                    top INTEGER NOT NULL,
                    left INTEGER NOT NULL
                );
                """
                              
class MySQL:
    def __init__(self, db_name: str):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()
        
        self.cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='books';")  
        result = self.cursor.fetchone()  
        print(result)
        if result is None:
            self.cursor.execute(createBookDB)
            self.CommitTable()
            
        self.cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='angles';")  
        result = self.cursor.fetchone()  
        print(result)
        if result is None:
            self.cursor.execute(createAngleDB)
            self.CommitTable()
        
    def __del__(self):
        self.cursor.close()
        self.conn.close()
    
    def CreateTable(self, table_name: str):
        self.cursor.execute(table_name)
        
    def InsertTableBook(self, data):
        try:
            self.cursor.executemany("insert into books (book_name, top, left) values(?, ?, ?)", data)
            self.CommitTable()
        except sqlite3.IntegrityError:  
            print("Unique key conflict. Record not inserted.")
    
    def InsertTableAngle(self, data):
        try:
            self.cursor.executemany("insert into angles (book_name, xAngle, yAngle) values(?, ?, ?)", data)
            self.CommitTable()
        except sqlite3.IntegrityError:  
            print("Unique key conflict. Record not inserted.")
                 
    def CommitTable(self):
        self.conn.commit()
        
    def SelectTableBook(self):
        self.cursor.execute(f"select * from books")
        # 获取所有查询结果  1
        rows = self.cursor.fetchall()  
        # 遍历并打印每一行结果  
        for row in rows:  
            print(row)  
    
    def SelectTableAngle(self):
        self.cursor.execute(f"select * from angles")
        # 获取所有查询结果  1
        rows = self.cursor.fetchall()  
        # 遍历并打印每一行结果  
        for row in rows:  
            print(row)  
            
    def SearchTableBook(self, bookname : str):
        try:
            # 使用参数化查询，防止SQL注入
            self.cursor.execute("select * from books where book_name = ?", (bookname, ))
            result = self.cursor.fetchone()  # 直接获取查询结果
            
            # 找到数据
            print(f"查询结果：{result}")  # 打印调试信息
            return result
        except Exception as e:
            # 处理查询错误
            print(f"查询错误：{str(e)}")
            
    def SearchTableAngle(self, bookname : str):
        try:
            # 使用参数化查询，防止SQL注入
            self.cursor.execute("select * from angles where book_name = ?", (bookname, ))
            result = self.cursor.fetchone()  # 直接获取查询结果
            
            # 找到数据
            print(f"查询结果：{result}")  # 打印调试信息
            return result   

        except Exception as e:
            # 处理查询错误
            print(f"查询错误：{str(e)}")



